const express = require('express');
const router = express.Router();
const { pool } = require('../config/db');
const auth = require('../middleware/auth');

// 保留原有的upcoming路由
router.get('/upcoming', async (req, res) => {
  try {
    console.log('即将到来的考试路由被访问，参数:', req.query);
    const { userId } = req.query;
    
    if (!userId) {
      return res.status(400).json({ message: '缺少必要参数' });
    }
    
    // 获取当前日期
    const today = new Date().toISOString().split('T')[0];
    console.log('当前日期:', today);
    
    // 查询用户信息
    const userQuery = `SELECT * FROM users WHERE userId = ?`;
    const [userRows] = await pool.query(userQuery, [userId]);
    
    if (userRows.length === 0) {
      console.log('找不到用户信息');
      return res.json([]);
    }
    
    const user = userRows[0];
    console.log('用户信息:', user);
    
    // 查询用户所在班级ID
    const classQuery = `SELECT id FROM classes WHERE className = ? OR id = ?`;
    console.log('执行班级查询:', classQuery, [user.classId, user.classId]);
    const [classRows] = await pool.query(classQuery, [user.classId, user.classId]);
    
    if (classRows.length === 0) {
      console.log('找不到班级信息，尝试使用全局考试');
      
      // 如果找不到班级，查询所有班级的考试（classIds包含"all"的考试）
      const allExamsQuery = `
        SELECT e.id, e.courseName as name, 
               DATE_FORMAT(e.examTime, '%Y-%m-%d') as date, 
               CONCAT(DATE_FORMAT(e.examTime, '%H:%i'), '-', 
                     DATE_FORMAT(DATE_ADD(e.examTime, INTERVAL e.duration MINUTE), '%H:%i')) as time,
               CONCAT(b.name, ' ', cr.roomNumber) as location,
               'upcoming' as status
        FROM exams e
        LEFT JOIN buildings b ON e.buildingId = b.id
        LEFT JOIN classrooms cr ON e.classroomId = cr.id
        WHERE e.classIds LIKE '%"all"%'
        ORDER BY e.examTime
        LIMIT 5
      `;
      
      console.log('执行全局考试查询:', allExamsQuery);
      const [allExamsRows] = await pool.query(allExamsQuery);
      console.log('全局考试查询结果:', allExamsRows);
      return res.json(allExamsRows);
    }
    
    const classId = classRows[0].id;
    console.log('找到班级ID:', classId);
    
    // 查询考试信息
    const query = `
      SELECT e.id, e.courseName as name, 
             DATE_FORMAT(e.examTime, '%Y-%m-%d') as date, 
             CONCAT(DATE_FORMAT(e.examTime, '%H:%i'), '-', 
                   DATE_FORMAT(DATE_ADD(e.examTime, INTERVAL e.duration MINUTE), '%H:%i')) as time,
             CONCAT(b.name, ' ', cr.roomNumber) as location,
             'upcoming' as status
      FROM exams e
      LEFT JOIN buildings b ON e.buildingId = b.id
      LEFT JOIN classrooms cr ON e.classroomId = cr.id
      WHERE (e.classIds LIKE ? OR e.classIds LIKE '%"all"%')
      ORDER BY e.examTime
      LIMIT 5
    `;
    
    console.log('执行SQL查询:', query, [`%"${classId}"%`]);
    
    try {
      const [rows] = await pool.query(query, [`%"${classId}"%`]);
      console.log('查询结果:', rows);
      res.json(rows);
    } catch (dbError) {
      console.error('数据库查询失败:', dbError);
      res.json([]);
    }
  } catch (error) {
    console.error('获取即将到来的考试失败:', error);
    res.status(500).json({ message: '服务器错误', error: error.message });
  }
});

// 修改获取学生所有考试安排路由
router.get('/student', auth, async (req, res) => {
  try {
    const studentId = req.user.id;
    const status = req.query.status || '';
    
    let query = `
      SELECT 
        e.id,
        c.name AS courseName,
        c.courseCode,
        DATE_FORMAT(e.examTime, '%Y-%m-%d') as examDate,
        DATE_FORMAT(e.examTime, '%H:%i') as examTime,
        IFNULL(CONCAT(b.name, ' ', cr.roomNumber), '未安排') as location,
        u.name as teacher,
        CASE 
          WHEN e.examTime > NOW() THEN 'upcoming'
          WHEN e.examTime < NOW() THEN 'completed'
          ELSE 'ongoing'
        END as status
      FROM exams e
      LEFT JOIN courses c ON e.courseId = c.id
      LEFT JOIN users u ON c.teacherId = u.id
      LEFT JOIN buildings b ON e.buildingId = b.id
      LEFT JOIN classrooms cr ON e.classroomId = cr.id
      LEFT JOIN exam_class_relation ecr ON e.id = ecr.examId
      LEFT JOIN users s ON s.classId = ecr.classId
      WHERE s.id = ?
    `;
    
    const queryParams = [studentId];
    
    if (status) {
      if (status === 'upcoming') {
        query += ' AND e.examTime > NOW()';
      } else if (status === 'completed') {
        query += ' AND e.examTime < NOW()';
      }
    }
    
    query += ' ORDER BY e.examTime';
    
    console.log('执行考试查询:', query, queryParams);
    const [rows] = await pool.query(query, queryParams);
    
    res.json(rows);
  } catch (error) {
    console.error('获取学生考试安排失败:', error);
    res.status(500).json({ message: '服务器错误', error: error.message });
  }
});

// 删除重复的upcoming路由，保留上面的那个

// 修改获取单个考试详情的路由
router.get('/:id', auth, async (req, res) => {
  try {
    const examId = req.params.id;
    const studentId = req.user.id;
    
    // 先检查考试是否存在
    const checkExamQuery = `SELECT id FROM exams WHERE id = ?`;
    const [examCheck] = await pool.query(checkExamQuery, [examId]);
    
    if (examCheck.length === 0) {
      return res.status(404).json({ message: '考试不存在' });
    }
    
    // 移除不存在的字段 e.seatNumber 和 e.notes
    const query = `
      SELECT 
        e.id,
        c.name AS courseName,
        c.courseCode,
        DATE_FORMAT(e.examTime, '%Y-%m-%d') as examDate,
        DATE_FORMAT(e.examTime, '%H:%i') as examTime,
        IFNULL(CONCAT(b.name, ' ', cr.roomNumber), '未安排') as location,
        u.name as teacher,
        CASE 
          WHEN e.examTime > NOW() THEN 'upcoming'
          WHEN e.examTime < NOW() THEN 'completed'
          ELSE 'ongoing'
        END as status
      FROM exams e
      LEFT JOIN courses c ON e.courseId = c.id
      LEFT JOIN users u ON c.teacherId = u.id
      LEFT JOIN buildings b ON e.buildingId = b.id
      LEFT JOIN classrooms cr ON e.classroomId = cr.id
      LEFT JOIN exam_class_relation ecr ON e.id = ecr.examId
      LEFT JOIN users s ON s.classId = ecr.classId
      WHERE e.id = ? AND s.id = ?
      LIMIT 1
    `;
    
    console.log('执行考试详情查询:', query, [examId, studentId]);
    const [rows] = await pool.query(query, [examId, studentId]);
    
    if (rows.length === 0) {
      // 如果找不到学生关联的考试，尝试直接获取考试信息
      const fallbackQuery = `
        SELECT 
          e.id,
          c.name AS courseName,
          c.courseCode,
          DATE_FORMAT(e.examTime, '%Y-%m-%d') as examDate,
          DATE_FORMAT(e.examTime, '%H:%i') as examTime,
          IFNULL(CONCAT(b.name, ' ', cr.roomNumber), '未安排') as location,
          u.name as teacher,
          CASE 
            WHEN e.examTime > NOW() THEN 'upcoming'
            WHEN e.examTime < NOW() THEN 'completed'
            ELSE 'ongoing'
          END as status
        FROM exams e
        LEFT JOIN courses c ON e.courseId = c.id
        LEFT JOIN users u ON c.teacherId = u.id
        LEFT JOIN buildings b ON e.buildingId = b.id
        LEFT JOIN classrooms cr ON e.classroomId = cr.id
        WHERE e.id = ?
        LIMIT 1
      `;
      
      console.log('执行备用考试详情查询:', fallbackQuery, [examId]);
      const [fallbackRows] = await pool.query(fallbackQuery, [examId]);
      
      if (fallbackRows.length === 0) {
        return res.status(404).json({ message: '找不到考试信息' });
      }
      
      // 添加默认值
      const examData = {
        ...fallbackRows[0],
        seatNumber: '未分配',  // 添加默认座位号
        notes: '暂无备注'      // 添加默认备注
      };
      
      return res.json(examData);
    }
    
    // 添加默认值
    const examData = {
      ...rows[0],
      seatNumber: '未分配',  // 添加默认座位号
      notes: '暂无备注'      // 添加默认备注
    };
    
    res.json(examData);
  } catch (error) {
    console.error('获取考试详情失败:', error);
    res.status(500).json({ message: '服务器错误', error: error.message });
  }
});

// 添加获取学生课程表的路由
router.get('/student/timetable', auth, async (req, res) => {
  try {
    const studentId = req.user.id;
    // 限制周次范围在1-16周
    let week = parseInt(req.query.week) || 1;
    if (week > 16) week = 16; // 如果请求的周次超过16，则默认显示第16周
    const semester = req.query.semester || '2023-2024-1'; // 默认当前学期
    
    console.log('获取学生课程表，参数:', { studentId, week, semester });
    
    // 查询学生所在班级
    const userQuery = `SELECT classId FROM users WHERE id = ?`;
    const [userRows] = await pool.query(userQuery, [studentId]);
    
    if (userRows.length === 0) {
      return res.status(404).json({ message: '找不到学生信息' });
    }
    
    const classId = userRows[0].classId;
    console.log('学生所在班级ID:', classId);
    
    // 首先尝试直接查询学生与课程的关联
    try {
      // 修改查询字段，使用正确的数据库字段名
      const directQuery = `
        SELECT 
          c.id,
          c.name,
          c.courseCode,
          c.weekday,
          c.teacherId,
          c.credits,
          c.buildingId,
          c.classroomId,
          c.startTime,
          c.endTime,
          c.weeks,
          c.semester,
          u.name as teacher,
          b.name as buildingName,
          cr.roomNumber
        FROM courses c
        JOIN course_student_relation csr ON c.id = csr.courseId
        LEFT JOIN users u ON c.teacherId = u.id
        LEFT JOIN buildings b ON c.buildingId = b.id
        LEFT JOIN classrooms cr ON c.classroomId = cr.id
        WHERE csr.studentId = ? AND c.semester = ?
      `;
      
      console.log('执行直接查询学生课程:', directQuery, [studentId, semester]);
      const [directRows] = await pool.query(directQuery, [studentId, semester]);
      
      console.log('直接查询结果:', directRows.length ? '有数据' : '无数据');
      
      if (directRows.length > 0) {
        // 处理课程表数据
        const timetableData = directRows.map(course => {
          // 解析weeks字段
          let courseWeeks = [];
          try {
            if (typeof course.weeks === 'string') {
              courseWeeks = JSON.parse(course.weeks);
            } else if (Array.isArray(course.weeks)) {
              courseWeeks = course.weeks;
            }
          } catch (e) {
            console.error('解析weeks字段失败:', e);
            courseWeeks = Array.from({length: 20}, (_, i) => i + 1); // 默认1-20周都有课
          }
          
          // 确保weekday是数字
          const weekday = parseInt(course.weekday) || 1;
          
          // 从startTime和endTime计算出课程节次
          const startPeriod = calculatePeriodFromTime(course.startTime);
          const endPeriod = calculateEndPeriodFromTime(course.endTime, startPeriod);
          
          // 转换为前端需要的格式
          return {
            id: course.id,
            name: course.name,
            courseCode: course.courseCode,
            weekday: weekday,
            startPeriod: startPeriod,
            endPeriod: endPeriod,
            weeks: courseWeeks,
            teacher: course.teacher,
            location: `${course.buildingName || ''} ${course.roomNumber || '未指定'}`.trim()
          };
        });
        
        console.log('处理后的课程表数据:', JSON.stringify(timetableData, null, 2));
        
        return res.json({
          week,
          semester,
          timetable: timetableData
        });
      }
    } catch (error) {
      console.error('直接查询学生课程失败:', error);
      // 继续尝试其他查询方式
    }
    
    // 如果直接查询失败，尝试从班级课程表获取数据
    try {
      // 修改查询字段，使用正确的数据库字段名
      const timetableQuery = `
        SELECT 
          t.id,
          t.courseId,
          c.name,
          c.courseCode,
          c.weekday,
          c.teacherId,
          c.credits,
          c.buildingId,
          c.classroomId,
          c.startTime,
          c.endTime,
          c.weeks,
          c.semester,
          u.name as teacher,
          b.name as buildingName,
          cr.roomNumber
        FROM timetables t
        LEFT JOIN courses c ON t.courseId = c.id
        LEFT JOIN users u ON c.teacherId = u.id
        LEFT JOIN buildings b ON c.buildingId = b.id
        LEFT JOIN classrooms cr ON c.classroomId = cr.id
        WHERE t.classId = ? AND t.semester = ?
      `;
      
      console.log('执行班级课程表查询:', timetableQuery, [classId, semester]);
      const [timetableRows] = await pool.query(timetableQuery, [classId, semester]);
      
      console.log('班级课程表查询结果:', timetableRows.length ? '有数据' : '无数据');
      
      if (timetableRows.length > 0) {
        // 处理课程表数据
        const timetableData = timetableRows.map(course => {
          // 解析weeks字段
          let courseWeeks = [];
          try {
            if (typeof course.weeks === 'string') {
              courseWeeks = JSON.parse(course.weeks);
            } else if (Array.isArray(course.weeks)) {
              courseWeeks = course.weeks;
            }
          } catch (e) {
            console.error('解析weeks字段失败:', e);
            courseWeeks = [];
          }
          
          // 转换为前端需要的格式
          return {
            id: course.id,
            name: course.name,
            courseCode: course.courseCode,
            weekday: course.weekday,
            // 从startTime和endTime计算出课程节次
            startPeriod: calculatePeriodFromTime(course.startTime),
            endPeriod: calculatePeriodFromTime(course.endTime),
            weeks: courseWeeks,
            teacher: course.teacher,
            location: `${course.buildingName || ''} ${course.roomNumber || '未指定'}`
          };
        });
        
        return res.json({
          week,
          semester,
          timetable: timetableData
        });
      }
    } catch (error) {
      console.error('班级课程表查询失败:', error);
      // 继续尝试其他查询方式
    }
    
    // 如果前两种方式都失败，尝试直接从课程表获取数据
    try {
      // 修改查询字段，使用正确的数据库字段名
      const courseQuery = `
        SELECT 
          c.id,
          c.name,
          c.courseCode,
          c.weekday,
          c.teacherId,
          c.credits,
          c.buildingId,
          c.classroomId,
          c.startTime,
          c.endTime,
          c.weeks,
          c.semester,
          u.name as teacher,
          b.name as buildingName,
          cr.roomNumber
        FROM courses c
        LEFT JOIN users u ON c.teacherId = u.id
        LEFT JOIN buildings b ON c.buildingId = b.id
        LEFT JOIN classrooms cr ON c.classroomId = cr.id
        WHERE c.semester = ?
      `;
      
      console.log('执行所有课程查询:', courseQuery, [semester]);
      const [courseRows] = await pool.query(courseQuery, [semester]);
      
      console.log('所有课程查询结果:', courseRows.length ? '有数据' : '无数据');
      
      // 处理课程表数据
      const timetableData = courseRows.map(course => {
        // 解析weeks字段
        let courseWeeks = [];
        try {
          if (typeof course.weeks === 'string') {
            courseWeeks = JSON.parse(course.weeks);
          } else if (Array.isArray(course.weeks)) {
            courseWeeks = course.weeks;
          }
        } catch (e) {
          console.error('解析weeks字段失败:', e);
          courseWeeks = [];
        }
        
        // 转换为前端需要的格式
        return {
          id: course.id,
          name: course.name,
          courseCode: course.courseCode,
          weekday: course.weekday,
          // 从startTime和endTime计算出课程节次
          startPeriod: calculatePeriodFromTime(course.startTime),
          endPeriod: calculatePeriodFromTime(course.endTime),
          weeks: courseWeeks,
          teacher: course.teacher,
          location: `${course.buildingName || ''} ${course.roomNumber || '未指定'}`
        };
      });
      
      return res.json({
        week,
        semester,
        timetable: timetableData
      });
    } catch (error) {
      console.error('所有课程查询失败:', error);
      return res.json({
        week,
        semester,
        timetable: []
      });
    }
  } catch (error) {
    console.error('获取学生课程表失败:', error);
    res.status(500).json({ message: '服务器错误', error: error.message });
  }
});

// 添加辅助函数，根据时间计算课程节次
function calculatePeriodFromTime(timeStr) {
  if (!timeStr) return 1;
  
  try {
    const hour = parseInt(timeStr.split(':')[0]);
    
    if (hour < 10) return 1; // 1-2节
    if (hour < 12) return 3; // 3-4节
    if (hour < 15) return 5; // 5-6节
    if (hour < 17) return 7; // 7-8节
    return 9; // 9-10节
  } catch (e) {
    console.error('计算课程节次失败:', e);
    return 1;
  }
}

// 添加新的辅助函数，计算结束节次
function calculateEndPeriodFromTime(timeStr, startPeriod) {
  if (!timeStr) return startPeriod + 1;
  
  try {
    const hour = parseInt(timeStr.split(':')[0]);
    const minute = parseInt(timeStr.split(':')[1]);
    
    // 根据结束时间计算结束节次
    if (hour < 10 || (hour === 10 && minute <= 30)) return 2; // 1-2节
    if (hour < 12 || (hour === 12 && minute <= 30)) return 4; // 3-4节
    if (hour < 15 || (hour === 15 && minute <= 30)) return 6; // 5-6节
    if (hour < 17 || (hour === 17 && minute <= 30)) return 8; // 7-8节
    return 10; // 9-10节
  } catch (e) {
    console.error('计算结束节次失败:', e);
    return startPeriod + 1;
  }
}

// 添加根据课程ID获取考试信息的路由
router.get('/courses/:courseId', auth, async (req, res) => {
  try {
    const courseId = req.params.courseId;
    const studentId = req.user.id;
    
    console.log('根据课程ID获取考试信息，参数:', { courseId, studentId });
    
    // 查询学生所在班级
    const userQuery = `SELECT classId FROM users WHERE id = ?`;
    const [userRows] = await pool.query(userQuery, [studentId]);
    
    if (userRows.length === 0) {
      return res.status(404).json({ message: '找不到学生信息' });
    }
    
    const classId = userRows[0].classId;
    console.log('学生所在班级ID:', classId);
    
    // 查询与该课程相关的考试
    const query = `
      SELECT 
        e.id,
        c.name AS courseName,
        c.courseCode,
        DATE_FORMAT(e.examTime, '%Y-%m-%d') as examDate,
        DATE_FORMAT(e.examTime, '%H:%i') as examTime,
        IFNULL(CONCAT(b.name, ' ', cr.roomNumber), '未安排') as location,
        u.name as teacher,
        e.duration,
        CASE 
          WHEN e.examTime > NOW() THEN 'upcoming'
          WHEN e.examTime < NOW() THEN 'completed'
          ELSE 'ongoing'
        END as status
      FROM exams e
      LEFT JOIN courses c ON e.courseId = c.id
      LEFT JOIN users u ON c.teacherId = u.id
      LEFT JOIN buildings b ON e.buildingId = b.id
      LEFT JOIN classrooms cr ON e.classroomId = cr.id
      LEFT JOIN exam_class_relation ecr ON e.id = ecr.examId
      WHERE e.courseId = ? AND (ecr.classId = ? OR e.classIds LIKE '%"all"%')
      ORDER BY e.examTime
    `;
    
    console.log('执行考试查询:', query, [courseId, classId]);
    const [rows] = await pool.query(query, [courseId, classId]);
    
    // 如果没有找到考试，尝试直接查询课程的考试
    if (rows.length === 0) {
      const fallbackQuery = `
        SELECT 
          e.id,
          c.name AS courseName,
          c.courseCode,
          DATE_FORMAT(e.examTime, '%Y-%m-%d') as examDate,
          DATE_FORMAT(e.examTime, '%H:%i') as examTime,
          IFNULL(CONCAT(b.name, ' ', cr.roomNumber), '未安排') as location,
          u.name as teacher,
          e.duration,
          CASE 
            WHEN e.examTime > NOW() THEN 'upcoming'
            WHEN e.examTime < NOW() THEN 'completed'
            ELSE 'ongoing'
          END as status
        FROM exams e
        LEFT JOIN courses c ON e.courseId = c.id
        LEFT JOIN users u ON c.teacherId = u.id
        LEFT JOIN buildings b ON e.buildingId = b.id
        LEFT JOIN classrooms cr ON e.classroomId = cr.id
        WHERE e.courseId = ?
        ORDER BY e.examTime
      `;
      
      console.log('执行备用考试查询:', fallbackQuery, [courseId]);
      const [fallbackRows] = await pool.query(fallbackQuery, [courseId]);
      
      return res.json(fallbackRows);
    }
    
    res.json(rows);
  } catch (error) {
    console.error('获取课程考试信息失败:', error);
    res.status(500).json({ message: '服务器错误', error: error.message });
  }
});

// 添加获取课程考试信息的路由（兼容旧版API路径）
router.get('/courses', auth, async (req, res) => {
  try {
    const courseId = req.query.courseId;
    
    if (!courseId) {
      return res.status(400).json({ message: '缺少课程ID参数' });
    }
    
    const studentId = req.user.id;
    
    console.log('根据课程ID获取考试信息(兼容路径)，参数:', { courseId, studentId });
    
    // 查询学生所在班级
    const userQuery = `SELECT classId FROM users WHERE id = ?`;
    const [userRows] = await pool.query(userQuery, [studentId]);
    
    if (userRows.length === 0) {
      return res.status(404).json({ message: '找不到学生信息' });
    }
    
    const classId = userRows[0].classId;
    
    // 查询与该课程相关的考试
    const query = `
      SELECT 
        e.id,
        c.name AS courseName,
        c.courseCode,
        DATE_FORMAT(e.examTime, '%Y-%m-%d') as examDate,
        DATE_FORMAT(e.examTime, '%H:%i') as examTime,
        IFNULL(CONCAT(b.name, ' ', cr.roomNumber), '未安排') as location,
        u.name as teacher,
        e.duration,
        CASE 
          WHEN e.examTime > NOW() THEN 'upcoming'
          WHEN e.examTime < NOW() THEN 'completed'
          ELSE 'ongoing'
        END as status
      FROM exams e
      LEFT JOIN courses c ON e.courseId = c.id
      LEFT JOIN users u ON c.teacherId = u.id
      LEFT JOIN buildings b ON e.buildingId = b.id
      LEFT JOIN classrooms cr ON e.classroomId = cr.id
      LEFT JOIN exam_class_relation ecr ON e.id = ecr.examId
      WHERE e.courseId = ? AND (ecr.classId = ? OR e.classIds LIKE '%"all"%')
      ORDER BY e.examTime
    `;
    
    const [rows] = await pool.query(query, [courseId, classId]);
    
    // 如果没有找到考试，尝试直接查询课程的考试
    if (rows.length === 0) {
      const fallbackQuery = `
        SELECT 
          e.id,
          c.name AS courseName,
          c.courseCode,
          DATE_FORMAT(e.examTime, '%Y-%m-%d') as examDate,
          DATE_FORMAT(e.examTime, '%H:%i') as examTime,
          IFNULL(CONCAT(b.name, ' ', cr.roomNumber), '未安排') as location,
          u.name as teacher,
          e.duration,
          CASE 
            WHEN e.examTime > NOW() THEN 'upcoming'
            WHEN e.examTime < NOW() THEN 'completed'
            ELSE 'ongoing'
          END as status
        FROM exams e
        LEFT JOIN courses c ON e.courseId = c.id
        LEFT JOIN users u ON c.teacherId = u.id
        LEFT JOIN buildings b ON e.buildingId = b.id
        LEFT JOIN classrooms cr ON e.classroomId = cr.id
        WHERE e.courseId = ?
        ORDER BY e.examTime
      `;
      
      const [fallbackRows] = await pool.query(fallbackQuery, [courseId]);
      
      return res.json(fallbackRows);
    }
    
    res.json(rows);
  } catch (error) {
    console.error('获取课程考试信息失败:', error);
    res.status(500).json({ message: '服务器错误', error: error.message });
  }
});

module.exports = router;
    